let db =require('../db');

//获取住户信息列表

exports.userList = async function (villageId,buildingId) {
  let sql=`
  SELECT 
  us.id,
  us.name,
  us.realName,
  us.phone,
  us.avatar,
  us.villageId,
  us.buildingId,
  us.unitId,
  us.houseNumber,  
   DATE_FORMAT(us.createdAt,'%Y-%m-%d %H:%i') AS createdAt,
   vi.name AS villageName,
   bu.name AS buildingName,
   un.name  AS unitName 
  FROM 
  (SELECT *FROM user WHERE isdelete='n') AS us
  LEFT JOIN village  AS vi ON vi.id  = us.villageId
  LEFT JOIN building  AS bu ON bu.id = us.buildingId
  LEFT JOIN unit AS un  ON un.id = us.unitId

  `
  let where = '';

  if( villageId && villageId!=0){
     where+=` AND us.villageId =:villageId`
  }
  if(buildingId){
     where+=` AND  us.buildingId =:buildingId`
  }
 
  sql += where.replace('AND', 'WHERE');
   sql+=` ORDER By us.createdAt DESC  `
  let data = await db.query(sql,{villageId,buildingId});
  return data[0];

}

//验证门牌号唯一性

exports.validationHouseNumberByUnit = async function(unitId,houseNumber,id){
  let sql=`
  SELECT * FROM  user where isdelete ='n' AND unitId =:unitId AND houseNumber =:houseNumber
  `
  if(id){
    sql+=` AND id !=:id`
  }
  let data = await db.query(sql,{unitId,houseNumber,id});
  return data[0];
}

//验证手机号唯一性

exports.validationPhone  =async function(phone,id){
  let  sql=`
   SELECT *FROM user where isdelete ='n' AND phone =:phone
  `
  if(id){
    sql+=` AND id !=:id`
  }
  let data = await db.query(sql,{phone,id});
  return data[0];
}

//添加住户
exports.addResident  = async function(villageId,buildingId,unitId,houseNumber,phone,realName){
  let sql=`
  INSERT  INTO user(realName,phone,villageId,buildingId,unitId,houseNumber)
  VALUES(:realName,:phone,:villageId,:buildingId,:unitId,:houseNumber)

  `
  let data =await db.query(sql,{villageId,buildingId,unitId,houseNumber,phone,realName});
  return data[0];
}

//获取单个住户信息
exports.getResidentsDetail = async function(id){
let sql=`
SELECT *FROM user where isdelete ='n' AND id =:id

`
let data  =await db.query(sql,{id});
return data[0][0];
}

//更新住户信息

exports.updateResidents = async function(id,villageId,buildingId,unitId,houseNumber,phone,realName){
  let sql=`
  UPDATE  user SET 
  villageId =:villageId,
  buildingId=:buildingId,
  unitId =:unitId,
  houseNumber =:houseNumber,
  phone =:phone,
  realName =:realName
  where id=:id
  
  `
  let data =await db.query(sql,{id,villageId,buildingId,unitId,houseNumber,phone,realName});
  return data[0];
}

//删除
exports.delResidents = async function(id){
  let sql=`
  UPDATE  user SET isdelete ='y'  where id =:id
  `
  let data  =await db.query(sql,{id});
  return data[0];
}
//删除该住户下的家庭信息

exports.delFamilyByUserId = async function(userId){
  let sql=`
  UPDATE  family SET  isdelete ='y' WHERE userId =:userId
  `
  let data= await db.query(sql,{userId});
  return data[0];
}

//获取该住户下所有的家庭信息

exports.getFamilyByUserId  = async function(userId){
  let  sql=`
    SELECT
    id, 
    name,
    url,
    unitId,
    isdelete
    FROM family  WHERE  userId= :userId  `
  let  data  =await  db.query(sql,{userId});
  return data[0];
}

// 获取该住户所对应的设备编号

exports.getEquipmentByUserId  = async function(userId){
  let sql=`
  SELECT 
eq.SerialNum,
eq.mac

FROM 

(SELECT *FROM user WHERE isdelete='n' AND id=:userId) AS u
LEFT JOIN equipment  AS eq ON eq.unitId = u.unitId  AND  eq.isdelete='n'
  `
let data   =await db.query(sql,{userId});
return data[0];
}

//当住户的单元信息修改时修改家庭成员的unit

exports.updateFamilyByResident  = async  function(unitId,id){
let  sql=`
   update family  set  unitId =:unitId  where userId =:id  and  isdelete ='n'
`
 let  data  =  await db.query(sql,{unitId,id});
 return data[0];
}